package com.lanqiao.gxnushop.mapper;

import com.lanqiao.gxnushop.entity.UserLogins;
import com.lanqiao.gxnushop.valueObject.RegisterCount;
import com.lanqiao.gxnushop.valueObject.yhClass;
import org.apache.ibatis.annotations.*;

import java.util.ArrayList;

@Mapper
public interface UserLoginsMapper {



    /**增加用户，用户注册
     * 传入的参数需要有   用户密码 */
    @Insert("insert into userLogins(userId,account,password,yhsf,zcsj) values (#{userId},#{account},#{password},#{yhsf},now())")
    boolean add (UserLogins userLogins);

    /**通过userId删除用户  返回boolean */
    @Delete("delete from userLogins where userId = #{userId}")
    boolean delete (String userId);

    /**通过userId修改登录密码    返回boolean*/
    @Update("update userLogins set password = #{password} where userId = #{userId}")
    boolean update (UserLogins userLogins);

    /**用账户密码，查找用户，返回UserLogins对象*/
    @Select("select * from userLogins where account = #{account} and password = #{password}")
    UserLogins select (UserLogins userLogins );

    /**通过userId查找用户，返回UserLogins对象*/
    @Select("select * from userLogins where userId = #{userId}")
    UserLogins findById(String userId);

    /** 近 6 个月 每个月买家的注册量 */
    @Select("SELECT MONTH(zcsj) AS month,SUM(CASE WHEN yhsf = '2' THEN 1 ELSE 0 END) AS count  \n" +
            "FROM UserLogins\n" +
            "WHERE zcsj >= CURDATE() - INTERVAL 6 MONTH\n" +
            "GROUP BY MONTH(zcsj)\t")
    ArrayList<RegisterCount> count();

    /** 近 6 个月 每个月买家的注册量 */
    @Select("SELECT MONTH(zcsj) AS month, SUM(CASE WHEN yhsf = '1' THEN 1 ELSE 0 END) AS count\n" +
            "FROM UserLogins\n" +
            "WHERE zcsj >= CURDATE() - INTERVAL 6 MONTH\n" +
            "GROUP BY MONTH(zcsj)")
    ArrayList<RegisterCount> countSeller();

    /**用户类型及数量*/
    @Select("select yhsf ,COUNT(*) as num from userlogins GROUP BY yhsf")
    ArrayList<yhClass> getYhClassCount();
}
